<?php

namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use DB;
use PDO;


class UserAccount extends Authenticatable
{
    use Notifiable;
	protected $table = 'user_account';


    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
         'remember_token',
    ];

    // 用户列表
	public static  function getUserAccountList($phone = ''){
		$sql = " select * from c_user_account ";
		if($phone){
			$sql.=" where phone like %'$phone'%";
		}
		$users = DB::select($sql);
		if(!$users){
			return array();
		}
		return $users;
	}

    // 获取用户所有车辆  1=正常  -1=伪删除
    public static function getUserCarInfo($uid){
        $userCars = DB::table('user_account')
            ->leftjoin('user_car', 'user_account.open_id', '=', 'user_car.open_id')
            ->leftjoin('car_brand', 'user_car.car_brand_id', '=', 'car_brand.id')
            ->leftjoin('car_type', 'user_car.car_type_id', '=', 'car_type.id')
            ->where('user_account.id',$uid)
            ->where('user_car.is_del',1)
            ->select('user_car.open_id', 'user_car.car_code','user_car.create_date','car_brand.brand_name','car_type.type_name')
//            ->toSql();
            ->get()
            ->toArray();
        if(!$userCars){
            return array();
        }
        return $userCars;

    }

    // 用户优惠券
	public static function getUserCoupons($uid){
		return $userCoupons = DB::table('user_account')
			->leftjoin('user_coupon', 'user_account.open_id', '=', 'user_coupon.open_id')
			->leftjoin('coupon', 'user_coupon.coupon_id', '=', 'coupon.id')
			->where('user_account.id',$uid)
			->select('coupon.name','coupon.cType','coupon.mixMoney','coupon.discount','coupon.money','user_coupon.useStatus','user_coupon.source','user_coupon.start_time','user_coupon.end_time','user_coupon.created_at')
//            ->toSql();
			->get()
			->toArray();
		if(!$userCoupons){
			return array();
		}
		return $userCoupons;
	}

	// 用户订单
	public static function getUserOrder($uid){
		return $userOrders = DB::table('user_account')
			->leftjoin('order', 'user_account.open_id', '=', 'order.open_id')
			->where('user_account.id',$uid)
			->select('order.*')
//            ->toSql();
			->get()
			->toArray();
		if(!$userOrders){
			return array();
		}
		return $userOrders;
	}

	// 活跃用户统计列表
	public static  function getActiveUsersCount($phone = '',$orderNum=''){
		$dbh = 'mysql:host='.env('DB_HOST').';dbname='.env('DB_DATABASE').'';
		$db = new PDO($dbh, env('DB_USERNAME'), env('DB_PASSWORD'));

//     本地
//		$sql = " SELECT su.* FROM
//		(
//		SELECT a.id,a.open_id,a.nickname,COUNT(b.id) AS order_num, a.phone,a.create_date AS register_time,b.state,b.create_date AS order_time FROM c_user_account  AS a
//		LEFT JOIN (SELECT * FROM c_order AS d ORDER BY id DESC) AS b 	ON a.open_id=b.open_id
//		WHERE b.state<=4 AND b.state>=1
//		GROUP BY a.open_id
//		) AS su WHERE 1=1 ";

//服务器
		$sql = " SELECT su.* FROM
		(
		SELECT any_value(a.id) as id,any_value(a.open_id) as open_id,any_value(a.nickname) as nickname,COUNT(b.id) AS order_num, any_value(a.phone) as phone,any_value(a.create_date) AS register_time,any_value(b.state) as state,any_value(b.create_date) AS order_time FROM c_user_account  AS a
		LEFT JOIN (SELECT * FROM c_order AS d ORDER BY id DESC) AS b 	ON a.open_id=b.open_id
		WHERE b.state<=4 AND b.state>=1
		GROUP BY a.open_id
		) AS su WHERE 1=1";



		if($phone){
			$sql.=" AND phone like '%$phone%' ";
		}
		if($orderNum) {
			$useArr = explode('~', $orderNum);
			if (count($useArr) > 1) {
				$min = trim($useArr[0]);
				$max = trim($useArr[1]);
				$sql .= " and  order_num < " . $max . " and order_num >= " . $min;
			} else {
				$sql .= ' and order_num =' . $orderNum;
			}
		}
//return $sql;

		$users = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
		if(!$users){
			return array();
		}


//		$users = DB::select($sql);
		return $users;
	}

	// 统计用户注册量
	public static  function getAccountRegisterTotal($where = array()){
		$sql = "
		SELECT su.* FROM
		(			
		SELECT LEFT(create_date,10) AS times , COUNT(*) as accountTotal FROM c_user_account GROUP  BY times 
		) AS su WHERE 1=1 	
		";

		if($where){
			$sql.=" AND times >='".$where['startTime']."' AND times <='".$where['endTime']."'";
		}

		$accounts = DB::select($sql);
		if(!$accounts){
			return array();
		}
		return $accounts;

	}





}
